﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Aplicacao;
using System.Data.SqlClient;
using System.Data;

public partial class index : System.Web.UI.Page
{
    public string abaMarcada {
        get {
            if (IsPostBack)
                return "tab2";
            else
                return "tab1";
        }
    }

    Validate va;
    Usuario user = new Usuario();

    public string norte;
    public string sul;
    public string leste;
    public string oeste;
    public string centro;
    public string total;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        BancoDeDados bd = new BancoDeDados();
        SqlConnection connection = bd.iniciaConexao();

        if (IsPostBack)
        {
            va = new Validate();
            //escondo box de validação
            escurecer.Visible = false;
            boxCPF.Visible = false;

            //valido a página
            Page.Validate("cadastroOcorrencia");
            if (Page.IsValid)
                inserirUsuario(connection);
        }
        else
            ConsultarTotaisOcorrencias(connection);
    }

    

    private void inserirUsuario(SqlConnection connection)
    {
        connection.Open();
        Guid? jaCadastrado = null; 
        if(user.VerificaUsuarioCadastrado(fieldCpf.Text.ToString(), connection) != null)
            jaCadastrado = Guid.Parse(user.VerificaUsuarioCadastrado(fieldCpf.Text.ToString(), connection).Rows[0]["internauta_id"].ToString());

        if (jaCadastrado != null)
        {
            try
            {
                if (inserirOcorrencia(jaCadastrado, connection))
                    Response.Redirect("obrigado.aspx");
            }
            catch (SqlException ex)
            {
                ErrorServer.InnerHtml = ex.ToString();
                ErrorServer.Visible = true;
                connection.Close();
            }
            finally {
                connection.Close();
            }
        }
        else
        {
            string insert = @"INSERT INTO tb_internauta(internauta_id, nome, cpf, email, bairro) VALUES(@Guid, @nome, @cpf, @email, @bairro)";

            SqlCommand cmd = new SqlCommand(insert, connection);
            cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier);
            cmd.Parameters.Add("@nome", SqlDbType.NVarChar, 255);
            cmd.Parameters.Add("@cpf", SqlDbType.NVarChar, 11);
            cmd.Parameters.Add("@email", SqlDbType.NVarChar, 255);
            cmd.Parameters.Add("@bairro", SqlDbType.NVarChar, 11);

            SqlParameter internauta_id = new SqlParameter("@Guid", System.Guid.NewGuid());
            SqlParameter nome = new SqlParameter("@nome", fieldNome.Text);
            SqlParameter cpf = new SqlParameter("@cpf", fieldCpf.Text.ToString());
            SqlParameter email = new SqlParameter("@email", fieldEmail.Text);
            SqlParameter bairro = new SqlParameter("@bairro", fieldBairro.Text);

            internauta_id.SqlDbType = SqlDbType.UniqueIdentifier;
            nome.SqlDbType = SqlDbType.NVarChar;
            cpf.SqlDbType = SqlDbType.NVarChar;
            email.SqlDbType = SqlDbType.NVarChar;
            bairro.SqlDbType = SqlDbType.NVarChar;

            try
            {
                cmd.Parameters["@Guid"].Value = internauta_id.Value;
                cmd.Parameters["@nome"].Value = nome.Value;
                cmd.Parameters["@cpf"].Value = cpf.Value;
                cmd.Parameters["@email"].Value = email.Value;
                cmd.Parameters["@bairro"].Value = bairro.Value;
                cmd.ExecuteNonQuery();
                if (inserirOcorrencia((Guid)internauta_id.Value, connection))
                    Response.Redirect("obrigado.aspx");

            }
            catch (Exception ex)
            {
                ErrorServer.InnerHtml = ex.ToString();
                ErrorServer.Visible = true;
                connection.Close();
            }
            finally
            {
                connection.Close();
            }
        }
    }

    private bool inserirOcorrencia(Guid? internautaId, SqlConnection connection)
    {
        string insert = @"INSERT INTO tb_ocorrencias(ocorrencia_id, desc_crime, tipo_ocorrencia, regiao, endereco, internauta_id, data_cadastro, data_ocorrencia) VALUES(@Guid, @desc_crime, @tipo_ocorrencia, @regiao, @endereco, @internauta_id, @data_cadastro, @data_ocorrencia)";

        SqlCommand cmd = new SqlCommand(insert, connection);
        cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier);
        cmd.Parameters.Add("@desc_crime", SqlDbType.NVarChar, 500);
        cmd.Parameters.Add("@tipo_ocorrencia", SqlDbType.NVarChar, 100);
        cmd.Parameters.Add("@regiao", SqlDbType.Char, 10);
        cmd.Parameters.Add("@endereco", SqlDbType.NVarChar, 255);
        cmd.Parameters.Add("@internauta_id", SqlDbType.UniqueIdentifier);
        cmd.Parameters.Add("@data_cadastro", SqlDbType.DateTime);
        cmd.Parameters.Add("@data_ocorrencia", SqlDbType.DateTime);

        SqlParameter ocorrencia_id = new SqlParameter("@Guid", System.Guid.NewGuid());
        SqlParameter descCrime = new SqlParameter("@desc_crime", fieldDescOcorr.Text);
        SqlParameter TipoOcorrencia = new SqlParameter("@tipo_ocorrencia", fieldTPOcorr.SelectedValue);
        SqlParameter regiao = new SqlParameter("@regiao", fieldRegiao.SelectedValue);
        SqlParameter endereco = new SqlParameter("@endereco", fieldEndereco.Text);
        SqlParameter internauta_id = new SqlParameter("@internauta_id", internautaId);
        SqlParameter data_cadastro = new SqlParameter("@data_cadastro", DateTime.Now);
        SqlParameter data_ocorrencia = new SqlParameter("@data_ocorrencia", fieldDtOcorrencia.Text);

        internauta_id.SqlDbType = SqlDbType.UniqueIdentifier;
        descCrime.SqlDbType = SqlDbType.NVarChar;
        TipoOcorrencia.SqlDbType = SqlDbType.NVarChar;
        regiao.SqlDbType = SqlDbType.Char;
        endereco.SqlDbType = SqlDbType.NVarChar;
        internauta_id.SqlDbType = SqlDbType.UniqueIdentifier;
        data_cadastro.SqlDbType = SqlDbType.DateTime;
        data_ocorrencia.SqlDbType = SqlDbType.DateTime;

        try
        {
            cmd.Parameters["@Guid"].Value = ocorrencia_id.Value;
            cmd.Parameters["@desc_crime"].Value = descCrime.Value;
            cmd.Parameters["@tipo_ocorrencia"].Value = TipoOcorrencia.Value;
            cmd.Parameters["@regiao"].Value = regiao.Value;
            cmd.Parameters["@endereco"].Value = endereco.Value;
            cmd.Parameters["@internauta_id"].Value = internauta_id.Value;
            cmd.Parameters["@data_cadastro"].Value = data_cadastro.Value;
            cmd.Parameters["@data_ocorrencia"].Value = data_ocorrencia.Value;
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            ErrorServer.InnerHtml = ex.ToString();
            ErrorServer.Visible = true;
            return false;
        }
        return true;
    }

    private void ConsultarTotaisOcorrencias(SqlConnection con) {
        SqlDataAdapter da = new SqlDataAdapter("Select * from CountCrimes", con);
        DataSet ds = new DataSet();

        da.Fill(ds);
        
        norte = ds.Tables[0].Rows[0]["NORTE"].ToString();
        sul = ds.Tables[0].Rows[0]["SUL"].ToString();
        leste = ds.Tables[0].Rows[0]["LESTE"].ToString();
        oeste = ds.Tables[0].Rows[0]["OESTE"].ToString();
        centro = ds.Tables[0].Rows[0]["CENTRO"].ToString();
        total = ds.Tables[0].Rows[0]["TOTAL"].ToString();
    }

    public void ValidateCpf(object source, ServerValidateEventArgs args)
    {
        args.IsValid = va.validarCPF(fieldCpf);
    }

    public void ValidateEmail(object source, ServerValidateEventArgs args)
    {
        args.IsValid = va.ValidarEmail(fieldEmail);
    }
}